package proj.pt.model.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import proj.pt.model.Course;
import proj.pt.model.Department;
import proj.pt.model.Exam;
import proj.pt.model.Result;
import proj.pt.model.SubCourse;
import proj.pt.model.Subject;
import proj.pt.model.SubjectAllotment;
import proj.pt.model.User;

@SuppressWarnings(value = "unchecked")
public class DaoImpl extends HibernateDaoSupport implements Dao {
	
	// -- Department related  
	public List<Department> getDepartments(){
		return getHibernateTemplate().find("from Department");
	}
	public Department getDepartment(Long id){
		Criteria c = getSession().createCriteria(Department.class);
		c.add(Restrictions.eq("id", new Long(id)));
		return (Department)c.uniqueResult(); 
	}
	public void saveOrUpdateDepartment(Department department){
		getHibernateTemplate().saveOrUpdate(department);
	}
	public void deleteDepartment(Department department){
		getHibernateTemplate().delete(department);
	}
	
	
	// -- Course related
	public List<Course> getCourses(){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Course c ");
		buffer.append("left join fetch c.department ");
		return getHibernateTemplate().find(buffer.toString());
	}
	
	public List<Course> getCourses(Department department){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Course c ");
		buffer.append("left join fetch c.department dept ");
		buffer.append("where dept = ? ");
		return getHibernateTemplate().find(buffer.toString(), department);
	}
	
	public Course getCourse(Long id){
		 StringBuilder buffer = new StringBuilder();
		 buffer.append("from Course c ");
		 buffer.append("left join fetch c.department ");
		 buffer.append("left join fetch c.subCourse ");
		 buffer.append("left join fetch c.batch ");
		 buffer.append("where c.id = ? ");
		 List list = getHibernateTemplate().find(buffer.toString(), id);
		 if(list != null && list.size()>0) return (Course)list.get(0);
		 return null;
	}
	public List<SubCourse> getSubCourses(){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubCourse sc ");
		buffer.append("order by sc.name ");
		return getHibernateTemplate().find(buffer.toString());
	}
	public SubCourse getSubCourse(Long id){
		Criteria c = getSession().createCriteria(SubCourse.class);
		c.add(Restrictions.eq("id", new Long(id)));
		return (SubCourse)c.uniqueResult(); 
	}
	public void saveOrUpdateCourse(Course course){
		getHibernateTemplate().saveOrUpdate(course);
	}
	public void deleteCourse(Course course){
		getHibernateTemplate().delete(course);
	}
	
	public SubCourse getSubCourse(String subCourseId){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubCourse sc ");
		buffer.append("where lower(sc.subCourseId) = lower(?) ");
		List list = getHibernateTemplate().find(buffer.toString(), subCourseId);
		if(list != null && list.size()>0) return (SubCourse)list.get(0);
		return null;
	}
	
	// -- Subject related
	public List<Subject> getSubjects(){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Subject s ");
		buffer.append("left join fetch s.subCourse sem ");
		buffer.append("left join fetch sem.course ");
		return getHibernateTemplate().find(buffer.toString());
	}
	public Subject getSubject(Long id){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Subject s ");
		buffer.append("left join fetch s.subCourse sc ");
		buffer.append("left join fetch sc.course ");
		buffer.append("where s.id = ? ");
		List list = getHibernateTemplate().find(buffer.toString(), id);
		if(list != null && list.size()>0) return (Subject)list.get(0);
		return null;
	}
	
	public List<Subject> getSubjects(SubCourse subCourse){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Subject s ");
		buffer.append("left join fetch s.subCourse sc ");
		buffer.append("where sc =  ?");
		return getHibernateTemplate().find(buffer.toString(), subCourse);
	}
	
	public void saveOrUpdateSubject(Subject subject){
		getHibernateTemplate().saveOrUpdate(subject);
	}
	public void deleteSubject(Subject subject){
		getHibernateTemplate().delete(subject);
	}
	/**
	 * Get Subject by given subject code.
	 * @param subjectCode
	 * @return
	 */
	public Subject getSubject(String subjectCode){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Subject s ");
		buffer.append("where lower(s.code) = lower(?) ");
		List list = getHibernateTemplate().find(buffer.toString(), subjectCode);
		if(list != null && list.size()>0) return (Subject)list.get(0);
		return null;
	}
	
	// -- User related
	public List<User> getUsers(){
		return getHibernateTemplate().find("from User");
	}
	public User getUser(Long id){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("left join fetch u.department ");
		buffer.append("left join fetch u.subCourse ");
		buffer.append("where u.id = ? ");
		List list = getHibernateTemplate().find(buffer.toString(), id);
		if(list != null && list.size()>0) return (User)list.get(0);
		return null;
	}
	
	public User getUserByStaffId(String staffId){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("where lower(u.staffId) = lower(?) ");
		List list = getHibernateTemplate().find(buffer.toString(), staffId);
		if(list != null && list.size()>0) return (User)list.get(0);
		return null;
	}
	
	public User getUserByStudentId(String studentId){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("where lower(u.studentId) = lower(?) ");
		List list = getHibernateTemplate().find(buffer.toString(), studentId);
		if(list != null && list.size()>0) return (User)list.get(0);
		return null;
	}
	
	public User getUserByRegistrationNo(String registrationNo){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("where lower(u.staffId) = lower(?) ");
		List list = getHibernateTemplate().find(buffer.toString(), registrationNo);
		if(list != null && list.size()>0) return (User)list.get(0);
		return null;
	}
	
	public void saveOrUpdateUser(User user){
		getHibernateTemplate().saveOrUpdate(user);
	}
	public void deleteUser(User user){
		getHibernateTemplate().delete(user);
	}
	public List<User> getUsers(List roles){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("where u.roleId in (:roleList) ");

		Query query = getSession().createQuery(buffer.toString());
		query.setParameterList("roleList", roles);
		
		return query.list();
	}
	public List<User> getUsers(Department department, List roles){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("left join fetch u.department ");
		buffer.append("where u.department = (:department) and u.roleId in (:roleList) ");
		
		Query query = getSession().createQuery(buffer.toString());
		query.setParameter("department", department);
		query.setParameterList("roleList", roles);
		
		return query.list();
	}
	public List<User> getUsers(SubCourse subCourse, List roles){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("left join fetch u.subCourse ");
		buffer.append("where u.subCourse = (:sbCourse) and u.roleId in (:roleList) ");

		Query query = getSession().createQuery(buffer.toString());
		query.setParameter("sbCourse", subCourse);
		query.setParameterList("roleList", roles);
		
		return query.list();
	}
	public List<User> getUsers(Course course, List roles){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from User u ");
		buffer.append("left join fetch u.subCourse sc ");
		buffer.append("left join fetch sc.course ");
		buffer.append("where sc.course = (:course) and u.roleId in (:roleList) ");

		Query query = getSession().createQuery(buffer.toString());
		query.setParameter("course", course);
		query.setParameterList("roleList", roles);
		
		return query.list();
	}
	public List<User> getUsersInResult(Exam exam){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Result  r ");
		buffer.append("inner join fetch r.exam  exm ");
		buffer.append("where exm = (:exam) ");
		Query query = getSession().createQuery(buffer.toString());
		query.setParameter("exam", exam);		
		List<Result> results =  query.list();
		
		if(results == null || results.size() < 1) return new ArrayList<User>();
		
		buffer = new StringBuilder();
		buffer.append("Select r.student from Result  r ");
		buffer.append("where r in (:rList) ");

		query = getSession().createQuery(buffer.toString());
		query.setParameterList("rList", results);		
		return query.list();
	}
	
	
	// -- Exam related
	public List<Exam> getExams(){
		return getHibernateTemplate().find("from Exam");
	}
	public Exam getExam(Long id){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("left join fetch e.subCourse ");
		buffer.append("left join fetch e.subject ");
		buffer.append("where e.id = ? ");
		List list = getHibernateTemplate().find(buffer.toString(), id);
		if(list != null && list.size()>0) return (Exam)list.get(0);
		return null;
	}
	public void saveOrUpdateExams(Exam exam){
		getHibernateTemplate().saveOrUpdate(exam);
	}
	public void deleteExams(Exam exam){
		getHibernateTemplate().delete(exam);
	}
	public Exam getExam(String examId){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("where lower(e.examId) = (?) ");
		List list = getHibernateTemplate().find(buffer.toString(), examId);
		if(list != null && list.size()>0) return (Exam)list.get(0);
		return null;
	}
	
	public List<Exam> getExams(SubCourse subCourse){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("left join fetch e.subCourse sc ");
		buffer.append("where sc = ? ");
		return getHibernateTemplate().find(buffer.toString(), subCourse);
	}
	
	public List<Exam> getExams(SubCourse subCourse, String examType){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("left join fetch e.subCourse sc ");
		buffer.append("where sc = ? and e.examType = ? ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subCourse, examType});
	}
	public List<Exam> getExams(Subject subject, String examType){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("left join fetch e.subject s ");
		buffer.append("where s = ? and e.examType = ? order by e.examDate ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subject, examType});
	}
	
	public List<Exam> getExams(User user, SubCourse subCourse){
		System.out.println("getExams(User user, SubCourse subCourse) NOT IMPLIMENTED");
		/*StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e,  SubjectAllotment se ");
		buffer.append("left join fetch e.subCourse sc ");
		buffer.append("left join fetch e.subject s1 ");
		buffer.append("left join fetch se.tutor t ");
		buffer.append("left join fetch se.subject s2 ");
		buffer.append("where s1 = s2 and sc = ? and t = ? ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subCourse, user});*/
		return null;
	}
	
	public List<Exam> getExams(User user, SubCourse subCourse, String examType){
		System.out.println("getExams(User user, SubCourse subCourse, String examType) NOT IMPLIMENTED");
		/*StringBuilder buffer = new StringBuilder();
		buffer.append("from Exam e ");
		buffer.append("left join fetch e.subCourse sc ");
		buffer.append("where sc = ? and e.examType = ? ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subCourse, examType});*/
		return null;
	}
	
	
	// -- Subject allotment related
	public SubjectAllotment getSubjectAllotment(Long id){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor ");
		buffer.append("left join fetch sa.subCourse ");
		buffer.append("left join fetch sa.subject ");
		buffer.append("where sa.id = ? ");
		List list = getHibernateTemplate().find(buffer.toString(), id);
		if(list != null && list.size()>0) return (SubjectAllotment)list.get(0);
		return null;
	}
	public void saveOrUpdateSubjectAllotment(SubjectAllotment allotment){
		getHibernateTemplate().saveOrUpdate(allotment);
	}
	public void deleteSubjectAllotment(SubjectAllotment allotment){
		getHibernateTemplate().delete(allotment);
	}
	public List<SubjectAllotment> getSubjectAllotments(User tutor, SubCourse subCourse, String batchYear, Subject subject){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor t ");
		buffer.append("left join fetch sa.subCourse sc ");
		buffer.append("left join fetch sa.subject s ");
		buffer.append("where t = ? ");
		buffer.append("and t = ? ");
		buffer.append("and sc = ? ");
		buffer.append("and sa.batchYear = ? ");
		buffer.append("and s = ? ");
		return getHibernateTemplate().find(buffer.toString(), 
				new Object[]{tutor, subCourse, batchYear, subject});
	}
	
	public List<SubjectAllotment> getSubjectAllotments(User staff){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor t ");
		buffer.append("left join fetch sa.subCourse sc ");
		buffer.append("left join fetch sa.subject s ");
		buffer.append("where t = ? ");
		return getHibernateTemplate().find(buffer.toString(), staff);
	}
	public List<SubjectAllotment> getSubjectAllotments(Subject subject){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor t ");
		buffer.append("left join fetch sa.subCourse sc ");
		buffer.append("left join fetch sa.subject s ");
		buffer.append("where s = ? ");
		return getHibernateTemplate().find(buffer.toString(), subject);
	}
	public List<SubjectAllotment> getSubjectAllotments(SubCourse subCourse){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor ");
		buffer.append("left join fetch sa.subCourse sc ");
		buffer.append("left join fetch sa.subject ");
		buffer.append("where sc = ? ");
		return getHibernateTemplate().find(buffer.toString(), subCourse);
	}
	public List<SubjectAllotment> getSubjectAllotments(User staff, SubCourse subCourse){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from SubjectAllotment sa ");
		buffer.append("left join fetch sa.tutor t ");
		buffer.append("left join fetch sa.subCourse sc ");
		buffer.append("left join fetch sa.subject ");
		buffer.append("where sc = ? and t = ? ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subCourse, staff});
	}
	
	// -- Exam results related
	public List<Result> getExamResults(Exam exam){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Result result ");
		buffer.append("left join fetch result.student ");
		buffer.append("left join fetch result.exam exm ");
		buffer.append("where exm = ? ");
		return getHibernateTemplate().find(buffer.toString(), exam);
	}
	public void saveExamResult(List results){
		getHibernateTemplate().saveOrUpdateAll(results);
	}
	
	public List<Result> getExamResults(Subject subject, String examType, User student){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Result result ");
		buffer.append("left join fetch result.student st ");
		buffer.append("left join fetch result.exam e ");
		buffer.append("where st = ? ");
		buffer.append("and e.subject = ? ");
		buffer.append("and e.examType = ? ");
		buffer.append("order by e.examDate, e.id ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{student, subject, examType});
	}
	public List<Result> getExamResults(Subject subject, String examType){
		StringBuilder buffer = new StringBuilder();
		buffer.append("from Result result ");
		buffer.append("left join fetch result.student st ");
		buffer.append("left join fetch result.exam e ");
		buffer.append("where e.subject = ? ");
		buffer.append("and e.examType = ? ");
		buffer.append("order by e.examDate, e.id ");
		return getHibernateTemplate().find(buffer.toString(), new Object[]{subject, examType});
	}
}
